import serial
import serial.tools.list_ports
import pymysql
import tkinter as tk
from tkinter import ttk, messagebox
import threading
import socket

# 全局变量
ser = None
network_socket = None

# 数据库连接函数

def connect_db():
    host = host_entry.get()
    user = user_entry.get()
    password = password_entry.get()
    database = database_entry.get()
    try:
        connection = pymysql.connect(
            host=host,
            user=user,
            password=password,
            database=database,
            charset='utf8'
        )
        messagebox.showinfo("数据库连接成功", f"已成功连接到数据库 {database}")
        # 连接成功后，获取数据库中的表名
        cursor = connection.cursor()
        cursor.execute("SHOW TABLES")
        tables = cursor.fetchall()
        table_names = [table[0] for table in tables]
        table_combobox['values'] = table_names
        if table_names:
            table_combobox.current(0)
            # 获取第一个表的字段名
            cursor.execute(f"SHOW COLUMNS FROM {table_names[0]}")
            columns = cursor.fetchall()
            column_names = [column[0] for column in columns]
            column_listbox.delete(0, tk.END)
            for column in column_names:
                column_listbox.insert(tk.END, column)
        cursor.close()
        return connection
    except Exception as e:
        messagebox.showerror("数据库连接错误", f"连接数据库时出错: {e}")
        return None

# 获取可用串口列表

def get_serial_ports():
    ports = serial.tools.list_ports.comports()
    return [port.device for port in ports]

# 打开串口

def open_serial():
    global ser
    port = port_combobox.get()
    baudrate = int(baudrate_combobox.get())
    try:
        ser = serial.Serial(port, baudrate, timeout=1)
        messagebox.showinfo("串口打开成功", f"已成功打开串口 {port}")
        if use_db_var.get():  # 仅当勾选使用数据库时才连接数据库
            start_reading()
    except Exception as e:
        messagebox.showerror("串口打开失败", f"打开串口时出错: {e}")

# 关闭串口

def close_serial():
    global ser
    if ser and ser.is_open:
        ser.close()
        messagebox.showinfo("串口关闭成功", "已成功关闭串口")

# 读取串口数据并上传到数据库

def read_serial_data():
    global ser
    if use_db_var.get():  # 仅当勾选使用数据库时才连接数据库
        connection = connect_db()
        if not connection:
            return
        cursor = connection.cursor()
    try:
        while ser and ser.is_open:
            if ser.in_waiting > 0:
                data = ser.readline().decode('utf-8').strip()
                if data:
                    # 显示接收到的数据
                    received_text.insert(tk.END, f"接收到数据: {data}\n")
                    received_text.see(tk.END)
                    if use_db_var.get():  # 仅当勾选使用数据库时才插入数据到数据库
                        # 获取选择的表和字段
                        selected_table = table_combobox.get()
                        selected_columns = column_listbox.curselection()
                        column_names = [column_listbox.get(index) for index in selected_columns]
                        if selected_table and column_names:
                            # 构建插入语句
                            columns_str = ', '.join(column_names)
                            values_str = ', '.join(['%s'] * len(column_names))
                            insert_sql = f"INSERT INTO {selected_table} ({columns_str}) VALUES ({values_str})"
                            try:
                                cursor.execute(insert_sql, (data,) * len(column_names))
                                connection.commit()
                            except Exception as e:
                                messagebox.showerror("数据库插入错误", f"插入数据到数据库时出错: {e}")
    except Exception as e:
        messagebox.showerror("读取数据错误", f"读取串口数据时出错: {e}")
    finally:
        if use_db_var.get() and cursor:
            cursor.close()
        if use_db_var.get() and connection:
            connection.close()

# 开始读取数据

def start_reading():
    thread = threading.Thread(target=read_serial_data)
    thread.daemon = True
    thread.start()

# 网络连接

def network_connect():
    global network_socket
    ip = ip_entry.get()
    if use_port_var.get():  # 仅当勾选使用端口号时才获取端口号
        port = int(port_entry.get())
    else:
        port = 0  # 默认端口号为0
    protocol = protocol_combobox.get()
    try:
        if protocol == "TCP":
            network_socket = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
            if port != 0:
                network_socket.connect((ip, port))
                messagebox.showinfo("网络连接成功", f"已成功连接到 {ip}:{port} (TCP)")
            else:
                messagebox.showinfo("网络连接成功", f"已成功连接到 {ip} (TCP，未使用自定义端口)")
        elif protocol == "UDP":
            network_socket = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)
            if port != 0:
                messagebox.showinfo("网络连接成功", f"已成功配置 UDP 连接到 {ip}:{port}")
            else:
                messagebox.showinfo("网络连接成功", f"已成功配置 UDP 连接到 {ip} (未使用自定义端口)")
    except Exception as e:
        messagebox.showerror("网络连接失败", f"连接网络时出错: {e}")

# 关闭网络连接

def network_disconnect():
    global network_socket
    if network_socket:
        network_socket.close()
        messagebox.showinfo("网络连接关闭", "已成功关闭网络连接")

# 选择表时更新字段列表

def on_table_select(event):
    selected_table = table_combobox.get()
    if selected_table:
        connection = connect_db()
        if connection:
            cursor = connection.cursor()
            cursor.execute(f"SHOW COLUMNS FROM {selected_table}")
            columns = cursor.fetchall()
            column_names = [column[0] for column in columns]
            column_listbox.delete(0, tk.END)
            for column in column_names:
                column_listbox.insert(tk.END, column)
            cursor.close()
            connection.close()

# 刷新数据库信息

def refresh_db_info():
    connection = connect_db()
    if connection:
        cursor = connection.cursor()
        cursor.execute("SHOW TABLES")
        tables = cursor.fetchall()
        table_names = [table[0] for table in tables]
        table_combobox['values'] = table_names
        if table_names:
            table_combobox.current(0)
            # 获取第一个表的字段名
            cursor.execute(f"SHOW COLUMNS FROM {table_names[0]}")
            columns = cursor.fetchall()
            column_names = [column[0] for column in columns]
            column_listbox.delete(0, tk.END)
            for column in column_names:
                column_listbox.insert(tk.END, column)
        cursor.close()
        connection.close()

# 发送信息函数

def send_message():
    message = send_entry.get()
    if message:
        if ser and ser.is_open:
            try:
                ser.write(message.encode('utf-8'))
                received_text.insert(tk.END, f"发送信息: {message}\n")
                received_text.see(tk.END)
            except Exception as e:
                messagebox.showerror("发送信息错误", f"发送信息时出错: {e}")
        else:
            messagebox.showerror("串口未打开", "请先打开串口再发送信息")

# 创建主窗口
root = tk.Tk()
root.title("GuYue调试助手")

# 设置窗口的行和列权重，使界面元素能自适应窗口大小
root.grid_rowconfigure(0, weight=1)
root.grid_columnconfigure(0, weight=1)
root.grid_columnconfigure(1, weight=1)

# 美化界面，设置背景颜色和字体
root.configure(bg='#f0f0f0')
style = ttk.Style()
style.theme_use('clam')
style.configure('.', font=('Arial', 12), foreground='black', background='#f0f0f0')
style.configure('TButton', font=('Arial', 12), foreground='black', background='#d0d0d0')

# 功能分区（左侧）
function_frame = tk.Frame(root, bg='#f0f0f0')
function_frame.grid(row=0, column=0, sticky=tk.NSEW, padx=10, pady=10)

# 数据库配置分区

db_frame = tk.LabelFrame(function_frame, text="数据库配置", bg='#f0f0f0')
db_frame.grid(row=0, column=0, sticky=tk.NSEW, padx=10, pady=10)

# 使用数据库复选框
use_db_var = tk.IntVar()
use_db_checkbox = tk.Checkbutton(db_frame, text="使用数据库", variable=use_db_var, bg='#f0f0f0')
use_db_checkbox.grid(row=0, column=0, columnspan=2, sticky=tk.W)

# 数据库主机标签和输入框

host_label = tk.Label(db_frame, text="数据库主机:", bg='#f0f0f0')
host_label.grid(row=1, column=0, sticky=tk.W)
host_entry = tk.Entry(db_frame)
host_entry.insert(0, "localhost")
host_entry.grid(row=1, column=1, sticky=tk.EW)

# 数据库用户名标签和输入框

user_label = tk.Label(db_frame, text="数据库用户名:", bg='#f0f0f0')
user_label.grid(row=2, column=0, sticky=tk.W)
user_entry = tk.Entry(db_frame)
user_entry.insert(0, "root")
user_entry.grid(row=2, column=1, sticky=tk.EW)

# 数据库密码标签和输入框

password_label = tk.Label(db_frame, text="数据库密码:", bg='#f0f0f0')
password_label.grid(row=3, column=0, sticky=tk.W)
password_entry = tk.Entry(db_frame, show='*')
password_entry.insert(0, "password")
password_entry.grid(row=3, column=1, sticky=tk.EW)

# 数据库名标签和输入框

database_label = tk.Label(db_frame, text="数据库名:", bg='#f0f0f0')
database_label.grid(row=4, column=0, sticky=tk.W)
database_entry = tk.Entry(db_frame)
database_entry.insert(0, "test_db")
database_entry.grid(row=4, column=1, sticky=tk.EW)

# 数据库连接按钮

db_connect_button = ttk.Button(db_frame, text="连接数据库", command=connect_db)
db_connect_button.grid(row=5, column=0, columnspan=2, sticky=tk.EW)

# 选择表的下拉框

table_label = tk.Label(db_frame, text="选择表:", bg='#f0f0f0')
table_label.grid(row=6, column=0, sticky=tk.W)
table_combobox = ttk.Combobox(db_frame)
table_combobox.grid(row=6, column=1, sticky=tk.EW)
table_combobox.bind("<<ComboboxSelected>>", on_table_select)

# 选择字段的列表框

column_label = tk.Label(db_frame, text="选择字段:", bg='#f0f0f0')
column_label.grid(row=7, column=0, sticky=tk.W)
column_listbox = tk.Listbox(db_frame, selectmode=tk.MULTIPLE)
column_listbox.grid(row=7, column=1, sticky=tk.NSEW)

# 刷新数据库信息按钮

refresh_button = ttk.Button(db_frame, text="刷新", command=refresh_db_info)
refresh_button.grid(row=8, column=0, columnspan=2, sticky=tk.EW)

# 串口配置分区

serial_frame = tk.LabelFrame(function_frame, text="串口配置", bg='#f0f0f0')
serial_frame.grid(row=1, column=0, sticky=tk.NSEW, padx=10, pady=10)

# 串口号标签和下拉框

port_label = tk.Label(serial_frame, text="串口号:", bg='#f0f0f0')
port_label.grid(row=0, column=0, sticky=tk.W)
port_combobox = ttk.Combobox(serial_frame)
port_combobox['values'] = get_serial_ports()
if port_combobox['values']:
    port_combobox.current(0)
port_combobox.grid(row=0, column=1, sticky=tk.EW)

# 波特率标签和下拉框

baudrate_label = tk.Label(serial_frame, text="波特率:", bg='#f0f0f0')
baudrate_label.grid(row=1, column=0, sticky=tk.W)
baudrate_combobox = ttk.Combobox(serial_frame)
baudrate_combobox['values'] = ["4800", "9600", "14400", "19200", "38400", "57600", "115200"]
baudrate_combobox.current(1)
baudrate_combobox.grid(row=1, column=1, sticky=tk.EW)

# 打开串口按钮

open_button = ttk.Button(serial_frame, text="打开串口", command=open_serial)
open_button.grid(row=2, column=0, sticky=tk.EW)

# 关闭串口按钮

close_button = ttk.Button(serial_frame, text="关闭串口", command=close_serial)
close_button.grid(row=2, column=1, sticky=tk.EW)

# 网络配置分区

network_frame = tk.LabelFrame(function_frame, text="网络配置", bg='#f0f0f0')
network_frame.grid(row=2, column=0, sticky=tk.NSEW, padx=10, pady=10)

# 使用端口号复选框
use_port_var = tk.IntVar()
use_port_checkbox = tk.Checkbutton(network_frame, text="使用自定义端口号", variable=use_port_var, bg='#f0f0f0', command=lambda: port_entry.config(state=tk.NORMAL if use_port_var.get() else tk.DISABLED))
use_port_checkbox.grid(row=0, column=0, columnspan=2, sticky=tk.W)

# IP地址标签和输入框

ip_label = tk.Label(network_frame, text="本地IP地址:", bg='#f0f0f0')
ip_label.grid(row=1, column=0, sticky=tk.W)
ip_entry = tk.Entry(network_frame)
ip_entry.insert(0, "127.0.0.1")
ip_entry.grid(row=1, column=1, sticky=tk.EW)

# 端口号标签和输入框

port_label = tk.Label(network_frame, text="本地端口号:", bg='#f0f0f0')
port_label.grid(row=2, column=0, sticky=tk.W)
port_entry = tk.Entry(network_frame, state=tk.DISABLED)
port_entry.insert(0, "8080")
port_entry.grid(row=2, column=1, sticky=tk.EW)

# 网络协议标签和下拉框

protocol_label = tk.Label(network_frame, text="网络协议类型:", bg='#f0f0f0')
protocol_label.grid(row=3, column=0, sticky=tk.W)
protocol_combobox = ttk.Combobox(network_frame)
protocol_combobox['values'] = ["TCP", "UDP"]
protocol_combobox.current(0)
protocol_combobox.grid(row=3, column=1, sticky=tk.EW)

# 网络连接按钮

connect_button = ttk.Button(network_frame, text="网络连接", command=network_connect)
connect_button.grid(row=4, column=0, sticky=tk.EW)

# 网络断开按钮

disconnect_button = ttk.Button(network_frame, text="网络断开", command=network_disconnect)
disconnect_button.grid(row=4, column=1, sticky=tk.EW)

# 信息分区（右侧）
message_frame = tk.Frame(root, bg='#f0f0f0')
message_frame.grid(row=0, column=1, sticky=tk.NSEW, padx=10, pady=10)

# 接收信息窗口分区

receive_frame = tk.LabelFrame(message_frame, text="接收信息", bg='#f0f0f0')
receive_frame.grid(row=0, column=0, sticky=tk.NSEW, padx=10, pady=10)

received_text = tk.Text(receive_frame, height=20, width=60)
received_text.pack(fill=tk.BOTH, expand=True)

# 发送信息分区

send_frame = tk.LabelFrame(message_frame, text="发送信息", bg='#f0f0f0')
send_frame.grid(row=1, column=0, sticky=tk.NSEW, padx=10, pady=10)

send_entry = tk.Entry(send_frame, width=50)
send_entry.pack(side=tk.LEFT, padx=10, pady=10)

send_button = ttk.Button(send_frame, text="发送", command=send_message)
send_button.pack(side=tk.LEFT, padx=10, pady=10)

# 运行主循环
root.mainloop()